package edu.cnu.tpts.dao.impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import edu.cnu.tpts.bean.Course;
import edu.cnu.tpts.bean.Student;
import edu.cnu.tpts.bean.StudentScore;
import edu.cnu.tpts.dao.StudentDao;
import edu.cnu.tpts.util.DBManager;

public class StudentDaoImpl implements StudentDao {

	/**
	 * �ж�ѧ���¼�Ƿ�ɹ�
	 */
	public Student isStudent(String name, String password) {
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		Student student=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("select * from student where loginname=? and password=?");
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			rs=pstmt.executeQuery();
			if(rs.next()){
				int id=rs.getInt(1);
				student =new Student();
				student.setId(rs.getInt(1));
				student.setLoginName(name);
				
			}
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
		return student;
	}
	/**
	 * ����ѧ�Ų�ѯ
	 */
	public Student findById(int id) {
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		Student student=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("select * from student where id=?");
			pstmt.setInt(1, id);			
			rs=pstmt.executeQuery();
		    if (rs.next()){
			String loginname=rs.getString("loginname");
			String realname=rs.getString("realname");
			String sex=rs.getString("sex");
			String stuClass=rs.getString("stu_class");
			String stuDepartment=rs.getString("stu_department");
			student =new Student();
			student.setId(id);
			student.setLoginName(loginname);
			student.setSex(sex);
			student.setRealName(realname);
			student.setStuClass(stuClass);
			student.setStuDepartment(stuDepartment);			
		    }
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
		return student;
	}
	/**
	 * ����ѧ����Ϣ
	 */
	public void updateStudent(Student stu) {
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		Student student=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("update student set password=?,loginname=? where id=?");
			pstmt.setString(1, stu.getPassword());
			pstmt.setString(2, stu.getLoginName());	
			pstmt.setInt(3, stu.getId());	
			pstmt.executeUpdate();
		  
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
	
	}
	/**
	 * ��ѯָ��ѧ��ɼ��б�
	 */
	public List<StudentScore> findScores(int stuid){
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		List<StudentScore> scorelist=new ArrayList<StudentScore>();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("select * from student_score where stu_id="+stuid);
			rs=pstmt.executeQuery();
			while(rs.next()){
				StudentScore sscore=new StudentScore();
				sscore.setId(rs.getInt("id"));
				sscore.setCou_id(rs.getInt("cou_id"));
				PreparedStatement pstmt2=conn.prepareStatement("select * from course where id="+sscore.getCou_id());
				ResultSet rs2=pstmt2.executeQuery();
				rs2.next();
				sscore.setCourseName(rs2.getString("name"));
				sscore.setTaskScore(rs.getInt("task_score"));
				sscore.setTestScore(rs.getInt("test_score"));
				sscore.setTotalScore(rs.getInt("task_score")+rs.getInt("test_score"));
				sscore.setStu_id(stuid);
				scorelist.add(sscore);			
			}
			
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
		return scorelist;
		
	}
	public void addStudent(Student stu) {
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("insert into student(id,loginname,password,realname,sex,stu_department,stu_class) values(?,?,?,?,?,?,?)");
			pstmt.setInt(1, stu.getId());
			pstmt.setString(2, stu.getLoginName());
			pstmt.setString(3, stu.getPassword());	
			pstmt.setString(4, stu.getRealName());	
			pstmt.setString(5, stu.getSex());	
			pstmt.setString(6, stu.getStuDepartment());	
			pstmt.setString(7, stu.getStuClass());	
			pstmt.executeUpdate();
		  
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
		
	}
	public Student findByName(String name) {
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		Student student=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("select * from student where loginname=?");
			pstmt.setString(1, name);			
			rs=pstmt.executeQuery();
		    if (rs.next()){
			int id=rs.getInt("id");
		    String loginname=rs.getString("loginname");
			String realname=rs.getString("realname");
			String sex=rs.getString("sex");
			String stuClass=rs.getString("stu_class");
			String stuDepartment=rs.getString("stu_department");
			student =new Student();
			student.setId(id);
			student.setLoginName(loginname);
			student.setSex(sex);
			student.setRealName(realname);
			student.setStuClass(stuClass);
			student.setStuDepartment(stuDepartment);			
		    }
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
		return student;
	}
	public List<Student> getAll() {
		DBManager dbManager = new DBManager();
		Connection conn=dbManager.getConnection();
		List<Student> stulist=new ArrayList<Student>();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement("select * from student ");
			rs=pstmt.executeQuery();
			while(rs.next()){
				Student student=new Student();
				int id=rs.getInt("id");
				String loginname=rs.getString("loginname");
				String realname=rs.getString("realname");
				String sex=rs.getString("sex");
				String stuClass=rs.getString("stu_class");
				String stuDepartment=rs.getString("stu_department");
				student =new Student();
				student.setId(id);
				student.setLoginName(loginname);
				student.setSex(sex);
				student.setRealName(realname);
				student.setStuClass(stuClass);
				student.setStuDepartment(stuDepartment);
				stulist.add(student);
			}
			
		} catch (SQLException e) {
						e.printStackTrace();
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
		return stulist;
	}
	
	public List<Course> getStudentCourse(int id) {
		DBManager dbManager = new DBManager();
		List<Course> courseList = new ArrayList<Course>();
		Connection conn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			conn = dbManager.getConnection();
			 pstmt = conn
					.prepareStatement("select c.id,c.name from student s,course c,student_course sc where s.id=sc.stu_id and c.id=sc.cou_id and s.id=?  ");
			 pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Course	course =new Course();
				course.setId(rs.getInt("id"));
				course.setName(rs.getString("name"));
				courseList.add(course);
			}
			return courseList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally{
			dbManager.closeAll(rs, pstmt, conn);
		}
	}
	public static void main(String[] args) {
		StudentDao stuDao = new StudentDaoImpl();
		List <Student>stulist=stuDao.getAll();
		for(Student stu:stulist){
			System.out.println(stu.getId()+"  "+stu.getRealName());
		}
	}
	}
